import pulp  # 导入pulp库，用于线性规划和优化
import pandas as pd  # 导入pandas库，用于数据处理
import numpy as np  # 导入numpy库，用于数值计算
import openpyxl  # 导入openpyxl库，用于处理Excel文件

# 读取用户上传的耕地数据，文件路径为'附件1.xlsx'
file_path = '附件1.xlsx'
df_land = pd.read_excel(file_path, sheet_name='乡村的现有耕地')  # 读取"乡村的现有耕地"工作表

# 读取作物数据，文件路径为'附件2.xlsx'
file_path_2 = '附件2.xlsx'  # 指定正确的文件路径
df_crops = pd.read_excel(file_path_2, sheet_name='2023年统计的相关数据')  # 读取作物统计数据表单

# 定义豆类作物和豆类蔬菜的作物编号集合
bean_crops = {1, 2, 3, 4, 5}  # 定义豆类作物编号，用于后续的轮作约束
bean_vegs = {17, 18, 19}  # 定义豆类蔬菜编号，适用于双季作物的轮作约束

# 整理地块信息，提取地块名称和其面积，存储在字典A中
# 字典A的键为地块名称，值为相应的地块面积（单位为亩）
A = {row['地块名称']: row['地块面积/亩'] for _, row in df_land.iterrows()}

# 筛选出单季地块类型（平旱地、梯田、山坡地），并存储在列表中
plots_single_season = df_land[df_land['地块类型'].isin(['平旱地', '梯田', '山坡地'])]['地块名称'].tolist()

# 筛选出双季地块类型（水浇地、普通大棚、智慧大棚），并分别存储在不同列表中
plots_double_season = df_land[df_land['地块类型'].isin(['水浇地', '普通大棚', '智慧大棚'])]['地块名称'].tolist()
irrigated_land = df_land[df_land['地块类型'] == '水浇地']['地块名称'].tolist()  # 水浇地
greenhouse_land = df_land[df_land['地块类型'] == '普通大棚']['地块名称'].tolist()  # 普通大棚
smart_greenhouse_land = df_land[df_land['地块类型'] == '智慧大棚']['地块名称'].tolist()  # 智慧大棚

# 创建一个优化模型实例，目标是最大化净收益
model = pulp.LpProblem("Crop_Planting_Optimization", pulp.LpMaximize)

# 定义作物类型编号范围，分别对应单季地块和不同类型的双季地块
crops_single_season = list(range(1, 16))  # 单季作物编号为1-15
crops_double_season_irrigated = list(range(16, 38))  # 水浇地作物编号为16-37
crops_double_season_greenhouse = list(range(23, 42))  # 普通大棚作物编号为23-41
crops_double_season_smart = list(range(27, 35))  # 智慧大棚作物编号为27-34

# 处理作物销售单价列，将价格范围转换为平均值
# 如果单价是一个范围（如'5-7'），取其平均值
df_crops['销售单价(元/斤)'] = df_crops['销售单价/(元/斤)'].apply(
    lambda x: (float(x.split('-')[0]) + float(x.split('-')[1])) / 2 if isinstance(x, str) else x
)

# 生成字典来存储作物的销售单价、亩产量和种植成本
P = {row['作物编号']: row['销售单价(元/斤)'] for _, row in df_crops.iterrows()}  # 销售单价字典
Y = {row['作物编号']: row['亩产量/斤'] for _, row in df_crops.iterrows()}  # 亩产量字典
C = {row['作物编号']: row['种植成本/(元/亩)'] for _, row in df_crops.iterrows()}  # 种植成本字典

# 定义波动范围，用于稳健优化，考虑到市场价格、产量和种植成本的不确定性
price_uncertainty = 0.1  # 销售单价波动范围 ±10%
yield_uncertainty = 0.1  # 亩产量波动范围 ±10%
cost_uncertainty = 0.05  # 种植成本波动范围 ±5%

# 定义决策变量x[i][k][t][j]，表示地块i在年份t的第j季是否种植作物k
# x的取值范围为0到1，用于线性规划
years = list(range(2024, 2031))  # 考虑从2024年到2030年
x = pulp.LpVariable.dicts("x", (plots_single_season + plots_double_season,
                                crops_single_season + crops_double_season_irrigated + crops_double_season_greenhouse + crops_double_season_smart,
                                years, [1, 2]), 0, 1, cat='Continuous')

# 定义辅助二进制变量y1和y2，约束x的取值为0.5或1，确保种植决策的合理性
y1 = pulp.LpVariable.dicts("y1", (plots_single_season + plots_double_season,
                                  crops_single_season + crops_double_season_irrigated + crops_double_season_greenhouse + crops_double_season_smart,
                                  years, [1, 2]), 0, 1, cat='Binary')

y2 = pulp.LpVariable.dicts("y2", (plots_single_season + plots_double_season,
                                  crops_single_season + crops_double_season_irrigated + crops_double_season_greenhouse + crops_double_season_smart,
                                  years, [1, 2]), 0, 1, cat='Binary')

# 单季地块的目标函数，考虑价格、产量和成本的不确定性（稳健优化）
Z_single = pulp.lpSum(
    ((P[k] * (1 - price_uncertainty) * Y[k] * (1 - yield_uncertainty) * A[i]  # 销售收入，包含价格和产量的波动
      - C[k] * (1 + cost_uncertainty) * A[i])  # 种植成本，包含成本的波动
     * x[i][k][t][1])  # 决策变量
    for t in years
    for i in plots_single_season
    for k in crops_single_season
)

# 水浇地的目标函数，考虑稳健优化
Z_irrigated = pulp.lpSum(
    ((P[k] * (1 - price_uncertainty) * Y[k] * (1 - yield_uncertainty) * A[i]
      - C[k] * (1 + cost_uncertainty) * A[i])
     * (x[i][k][t][1] + x[i][k][t][2]))  # 第一季和第二季的总收益
    for t in years
    for i in irrigated_land
    for k in crops_double_season_irrigated
)

# 普通大棚的目标函数，考虑稳健优化
Z_greenhouse = pulp.lpSum(
    ((P[k] * (1 - price_uncertainty) * Y[k] * (1 - yield_uncertainty) * A[i]
      - C[k] * (1 + cost_uncertainty) * A[i])
     * (x[i][k][t][1] + x[i][k][t][2]))
    for t in years
    for i in greenhouse_land
    for k in crops_double_season_greenhouse
)

# 智慧大棚的目标函数，考虑稳健优化
Z_smart_greenhouse = pulp.lpSum(
    ((P[k] * (1 - price_uncertainty) * Y[k] * (1 - yield_uncertainty) * A[i]
      - C[k] * (1 + cost_uncertainty) * A[i])
     * (x[i][k][t][1] + x[i][k][t][2]))
    for t in years
    for i in smart_greenhouse_land
    for k in crops_double_season_smart
)

# 综合目标函数，最大化所有地块的总收益
model += Z_single + Z_irrigated + Z_greenhouse + Z_smart_greenhouse

# 为x变量添加约束，确保合理的种植决策
for i in plots_single_season + irrigated_land + greenhouse_land + smart_greenhouse_land:
    for k in crops_single_season + crops_double_season_irrigated + crops_double_season_greenhouse + crops_double_season_smart:
        for t in years:
            for j in [1, 2]:
                # x变量的值由y1和y2的组合确定
                model += x[i][k][t][j] == 0.5 * y1[i][k][t][j] + y2[i][k][t][j]
                # 保证y1和y2不同时为1
                model += y1[i][k][t][j] + y2[i][k][t][j] <= 1

# 添加作物选择约束，确保每块地只能选择特定的作物进行种植
for t in years:
    for i in plots_single_season:
        # 单季地块作物选择限制
        model += pulp.lpSum(x[i][k][t][1] for k in crops_single_season) == 1

    for i in irrigated_land:
        # 水浇地作物选择限制
        model += pulp.lpSum(x[i][k][t][1] for k in range(16, 35)) == 1
        model += pulp.lpSum(x[i][k][t][2] for k in [16, 35, 36, 37]) == 1

    for i in greenhouse_land:
        # 普通大棚作物选择限制
        model += pulp.lpSum(x[i][k][t][1] for k in range(17, 35)) == 1
        model += pulp.lpSum(x[i][k][t][2] for k in range(38, 42)) == 1

    for i in smart_greenhouse_land:
        # 智慧大棚作物选择限制
        model += pulp.lpSum(x[i][k][t][1] for k in range(17, 35)) == 1
        model += pulp.lpSum(x[i][k][t][2] for k in range(17, 35)) == 1

# 轮作约束，确保每块地在每连续三年内至少种植一次豆类作物
for t_start in range(2024, 2028):
    for i in plots_single_season:
        model += pulp.lpSum(x[i][k][t][1] for k in bean_crops for t in range(t_start, t_start + 3)) >= 1

    for i in irrigated_land + greenhouse_land + smart_greenhouse_land:
        model += pulp.lpSum(x[i][k][t][1] + x[i][k][t][2] for k in bean_vegs for t in range(t_start, t_start + 3)) >= 1

# 求解模型，获得最优种植方案
model.solve()
